4.3 MySQL

• What is MySQL?
• What is the format of databases in MySQL?
• What is SQL?
• Is MySQL a part of PHP? Is PHP a part of MySQL? What is the correct relation between them?
• What kind of queries do you think you need to use for dynamic web applications?

• We will use procedural MySQLi - mysqli_*()s.
• Three steps:
  1. Connect to a MySQL DB;
  2. Manipulate/use the DB with SQL statements;
  3. Close the connection
• Connect? Isn't the web server already connected to the MySQL server?
• What is a server?
• Read all in PHP Connect to the MySQL Server.
  • Isn't it dangerous to include username and password in a PHP file?
  • Can you read PHP code from a browser?
    No. Only the execution result will come to the browser. Username and password will not be visible from the browser, as long as they are not echoed.
  • How can you obtain username and password to access a MySQL database?
  • We will use procedural MySQLi.
  • Here is an example to access a MySQL database on cs.tru.ca. You should not forget that the PHP code accessing MySQL on cs.tru.ca is running on the same system. You also need to know that MySQL running on cs.tru.ca does not accept any access from outside. The host value should be 'localhost', not 'cs.tru.ca'.

    // mysqli_connect(host, MySQL_username, MySQL_password, db_name)
    $conn = mysqli_connect("localhost", "yourMySQLusername", "yourMySQLpassword", "yourDBname");  // not 'cs.tru.ca'
    if (mysqli_connect_errno())  // or if (!$conn)
        echo "Failed to connect to DB: " . mysqli_connect_error();
    else
        echo "Succeeded to connect to DB";
    mysqli_close($conn);
    

  • On cs.tru.ca, your MySQL username is the same as your cs.tru.ca username, your MySQL password is your default password, and your MySQL database name is 'C354_...' ('C354_' + your MySQL username).
  • Trial 1: Let's try to connect to your database on cs.tru.ca.
    <?php ??? ?> Submit the code to the server to execute PHP code
    
  • Can the above username be used to control everything in the MySQL server?

CRUD (Create, Read, Update, Delete) using a language, SQL (Structured Query Language)
• Create - Create a DB/table; insert a row into a table
• Read - Selete rows from a table
• Update - Update rows in a table
• Delete - Delete rows from a table; drop a table/DB

• SQL statements: CREATE DATABASE; CREATE TABLE
• Is it safe to allow a user to create a database?
• When do you use mysqli_query() and mysqli_error()?
• Read 'MySQL Data Types' in SQL Data Types for Various DBs.
• Read all in PHP Create MySQL Tables.
• What are PRIMARY KEY, NOT NULL, and AUTO_INCREMENT?
• Here is an example to create a table. Note that the names of table and columns are NOT string values that use ' ' or " ".

  // host, user name, password, db name
  $conn = mysqli_connect("localhost", ???, ???, ???);
  if (mysqli_connect_errno())
      echo "Failed to connect: " . mysqli_connect_error();
  else {
      try {
          $sql = "CREATE TABLE Persons(
              SSN INT PRIMARY KEY, 
              FirstName VARCHAR(30) NOT NULL, 
              LastName VARCHAR(30) NOT NULL, 
              Age INT
              )";
          if (mysqli_query($conn, $sql))
              echo "Table Persons created";
          else
              echo "Table Persons not created";
      }
      catch(mysqli_sql_exception $e) {
          echo "Something wrong with the SQL statement? Aready created?";
      }
      mysqli_close($conn);
  }
  

• Trial 2: Let's try to create a table named 'Persons' in your DB.
  <?php $conn = ???? if (mysqli_connect_errno()) echo "Failed to connect: " . mysqli_connect_error(); else { $sql = "??? ??? Persons( SSN INT PRIMARY KEY, FirstName VARCHAR(30) NOT NULL, LastName VARCHAR(30) NOT NULL, Age INT)"; if (????) echo "Table Persons created"; else echo "Error creating table: " . mysqli_error($conn); ???? } ?> Submit the code to the server to execute PHP code
  
• Now we know how to create a table. How can you insert a row into the table?

• What SQL statement is used to insert a row into a table?
• Here is an example.

  $conn = mysqli_connect('localhost', ????);
  if (mysqli_connect_errno())
      echo 'Failed to connect: ' . mysqli_connect_error();
  else {
      $sql = "insert into Persons values ('Dave', 'Smith', 23)";  // Do you remember all the columns?
      if (mysqli_query($conn, $sql))
          echo 'Table Persons updated';
      else
          echo 'Error updating table: ' . mysqli_error($conn);
      $sql = "??? ??? Persons ??? (999888777, 'Tom', ???, 18)";  // Tom Davis
      ???(???, $sql);
      $sql = "??? ??? Persons ??? (888777666, ???, 'Brown', 21)";  // John Brown
      ???($conn, ???);
      $sql = "??? ??? Persons ??? (888777666, 'John', 'Black', 21)";  // What if you insert similar values again?
      ???(???, ???);
      ???($conn);
  }
  

• Trial 3: Let's try to insert a row into the table for user management..
  <?php $conn = ????; if (mysqli_connect_errno()) echo 'Failed to connect: ' . mysqli_connect_error(); else { $sql = "??? ??? Persons VALUES ('Dave', 'Smith', 23)"; // Do you remember all the columns? if (????) echo 'Table Persons updated'; else echo 'Error updating table: ' . mysqli_error($conn); $sql = "??? ??? Persons ??? (999888777, 'Tom', ???, 18)"; // Tom Davis ???($conn, $sql); $sql = "???? Persons ??? (888777666, ???, 'Brown', 21)"; // John Brown ???? $sql = "???? Persons ??? (888777666, 'John', 'Black', 21)"; // What if ... ? ???? mysqli_close($conn);} ?> Submit the code to the server to execute PHP code
  
• I would like to read tables using a web browser. Is there any convenient tool to admin MySQL?
  • Read the first paragraph in phpMyAdmin.
  • Try now https://cs.tru.ca/phpmyadmin with your MySQL username and password, or with the username and password in the above example.
  • Change your password. (Click 'Server: localhost:...' at the top, and you will see 'Chage password' in 'General settings'.)
  • What else do you think you can do with phpMyAdmin?
• How can you read a table with PHP? All the rows at once?

• What SQL statement is used to select rows from a table?
• How to get the rows one by one from the selection result?
• Here is an example.

  $conn = ????;
  if (mysqli_connect_errno())
      echo 'Failed to connect: ' . mysqli_connect_error();
  else {
      $sql = "select FirstName, LastName from Persons";  // or, "select * from Persons"
                                                         // Column names are NOT strings.
      // $sql = "select FirstName, LastName 
      //         from Persons";  // Can you use multiple lines for a string value?
      $result = ????;  // The select query result is NOT an array of selected rows.
      echo mysqli_num_rows($result) . '<br>';  // 
      if (???($result) > 0)  // if the number of rows is > 0
          /*
          while ($row = mysqli_fetch_assoc($result))  // mysqli_fetch_assoc() returns an associative array, and mysqli_fetch_array() returns an indexed array.
              echo $row['FirstName'] . " " . ???['LastName'] . '<br>';
              // $row['FirstName'] . " " . $row['LastName'] . " " . $row['Age'] . '<br>';  // What if you try to print Age?
          */
          for ($i = 0; $i < ???($result); $i++) {
              $row = mysqli_fetch_assoc($result);
              echo $row['FirstName'] . " " . ???['LastName'] . '<br>';
          }
      mysqli_close($conn);
  }
  

• Why doesn't mysqli_query('select ...') return an array of all the selected rows?
  A lot of rows could be selected.
• How to count the number of selected rows?
• Trial 4: Let's try the above 'select' example.
  <?php ???? ?> Submit the code to the server to execute PHP code
  
• How to get all the rows at once?
  • Read all in PHP mysqli_fetch_all() Function.
  • What value is returned from the above function?
  • Is it a really good idea to use the above function? What if the table size is huge?
• How to store all the fetched rows into an array?

  $users = array();  // or, $users = [];
  while ($row = mysqli_fetch_assoc($result))
      $users[] = $row;  // or array_push(...)?
  // or
  $users = [];
  $i = 0;
  while ($row = mysqli_fetch_assoc($result))
      $users[$i++] = $row;  // $users is a linear array of associative arrays.
  // or
  $users = [];
  for ($i = 0; $i < mysqli_num_rows($result); $i++) {
      $row = mysqli_fetch_assoc($result);
      $users[$i] = $row;
  }
  

• Trial 4.5: Let's update the 'select' example in Trial 4 and save all the selected rows into a linear array.
  <?php ???? var_dump($users); ?> Submit the code to the server to execute PHP code
  
• Now you know how to insert a new row and select all rows. What else do you need to know?

• For what is the where clause used?
• Here is an example.

  $conn = mysqli_connect('???', '???', '???', '???');
  if (mysqli_connect_errno())
      echo 'Failed to connect to ???: ' . mysqli_connect_error();
  else {
      $sql = "select FirstName, LastName ??? Persons 
              where (Age > 20)";  // Which rows will be selected?
      $result = ???($conn, $sql);
      while ($row = ???($result))  // Fetch an associative array
          echo $row['FirstName'] . " " . $row['LastName'];
      mysqli_close($conn);
  }
  

• Trial 5: Let's try the above example.
  <?php $conn = ???('???', '???', '???', '???'); if (mysqli_connect_errno()) echo 'Failed to connect to ???: ' . ???(); else { $sql = "??? FirstName, LastName ??? Persons ??? (Age > 20)"; // Which rows will be selected? $result = ???($conn, $sql); while ($row = ???($result)) // Fetch an associative array echo $row['FirstName'] . " " . ???['LastName']; mysqli_close($conn); } ?> Submit the code to the server to execute PHP code
  
• What operator is used for equality checking?
• The where clause is like a boolean statement with operators and columns.
• Here is more information about the where clause - SQL WHERE Clause, SQL AND & OR Operators.
• Wouldn't it be nice to have a way to obtain sorted rows with a select statement?

• Here is an example.

  $conn = mysqli_connect('???', '???', '???', '???');
  if (mysqli_connect_errno())
      echo 'Failed to connect to ???: ' . mysqli_connect_error();
  else {
      $sql = "select FirstName, LastName 
              from Persons 
              where (Age < 30 and Age > 10)  // Comment here? 
                                             // Not a good idea 
                                             //   because this comment becomes a part of the string.
              order by Age DESC";  // DESC or ASC
      $result = mysqli_query($conn, $sql);
      $persons = [];
      $i = 0;
      while ($row = mysqli_fetch_assoc($result)) {
          $persons[$i] = $row;
          $i++;
      }
      for ($i = 0; $i < count($persons); $i++) {
          foreach($persons[$i] as $k => $v)
              echo $persons[$i][$k] . " => " . $persons[$i][$v] . "<br>";
      }
      mysqli_close($conn);
  }
  

• How to sort in an ascending order?
• Can you use multiple columns for sorting?
• Trial 6: Let's try the above example.
  The difference is to use a function to select rows that are sorted in ascending order. All sorted rows are saved in a linear array.
  Another function is used to construct <table> code from the array.
  <?php $conn = ???? if (mysqli_connect_errno()) echo 'Failed to connect to ???: ' . mysqli_connect_error(); else { $persons = ???? $str = ???? echo $str; mysqli_close($conn); } ???? { // a function to fetch rows and to return them in an array ???? $sql = "??? * ??? Persons ??? (Age < 30 ??? Age > 10) ??? ??? Age ???"; // ASC $result = ???($conn, $sql); $persons = []; $i = 0; while ($row = mysqli_fetch_assoc($result)) { ????; // save $row into the array $i++; } return ??? } ???? ($persons) { // a function to construct a table code from an array of associative arrays // this function returns the table code that is a string. $str = "<table border=1>"; $str .= "<tr>"; // the first row for table heads foreach($persons[???] as $k => $v) $str .= "<th>" . ???? . "</th>"; $str .= "</tr>"; for ($i = 0; $i < ????; $i++) { ???? } $str += ???; ???? } ?> Submit the code to the server to execute PHP code
  

• How to modify a row or rows in a table?
• Here is an example to update John Brown's age.

  $conn = mysqli_connect('???', '???', '???', '???');
  if (mysqli_connect_errno())
      echo 'Failed to connect to ???: ' . mysqli_connect_error();
  else {
      $sql = "update Persons 
              set Age = 27 
              where (FirstName = 'John' and LastName = ???)";  // Be careful that FirstName is a column name, but its data type is string. So 'John', not John.
      $result = mysqli_query($conn, $sql);  // the data type of $result?
      $result = mysqli_query($conn, "select * from Persons");
      $str = "<table>";
      while ($row = mysqli_fetch_assoc($result)) {
          $str .= ???;
          $str .= "<td>" . $row['FirstName'] . "</td>";
          $str .= ??? . $row[???] . ???;  // LastName
          $str .= ??? . $row['Age'] . ???;
          $str .= "</tr>";
      }
      $str .= ???;
      echo $str;
      mysqli_close($conn);
  }
  

• Trial 7: Let's try the above example.
  <?php $conn = mysqli_connect('???', '???', '???', '???'); if (mysqli_connect_errno()) echo 'Failed to connect to ???: ' . mysqli_connect_error(); else { $sql = "??? Persons ??? Age = 27 ??? (FirstName = 'John' and LastName = ???)"; // Update John Brown's age $result = ???(????); $result = ???($conn, "select * from Persons"); $str = "<table border='1'>"; while ($row = ???($result)) { ???? } $str .= ???; echo $str; mysqli_close($conn); } ?> Submit the code to the server to execute PHP code
  
• Now you know how to do CRUD (Create, Retrieve, Update and Delete) operations, i.e., select/insert/update. How can you delete a row?

• Here is an example.

  $conn = mysqli_connect('???', '???', '???', '???');
  if (mysqli_connect_errno())
      echo 'Failed to connect to ???: ' . mysqli_connect_error();
  else {
      $sql = "delete from Persons 
              where (FirstName = 'John' and LastName = ???)";  // Delete John Brown's record(s)
      $result = mysqli_query($conn, $sql);
      $result = mysqli_query($conn, "select * from Persons");
      $str = "<table>";
      while ($row = mysqli_fetch_assoc($result)) {
          $str .= ???;
          $str .= "<td>" . $row['FirstName'] . "</td>";
          $str .= ??? . $row[???] . ???;
          $str .= ??? . $row['Age'] . ???;
          $str .= "</tr>";
      }
      $str .= ???;
      echo $str;
      mysqli_close($conn);
  }
  

• Trial 8: Let's try the above example.
  <?php $conn = mysqli_connect('???', '???', '???', '???'); if (mysqli_connect_errno()) echo 'Failed to connect to ???: ' . mysqli_connect_error(); else { $sql = "??? ??? Persons ??? (??? = 'John' and LastName = ???)"; // Delete John Brown's record(s) $result = ???? $result = ???($conn, "select * from Persons"); $str = "<table>"; while ($row = ???($result)) { ???? } $str .= ???; echo $str; mysqli_close($conn); } ?> Submit the code to the server to execute PHP code
  
• How to delete all rows?
  • DELETE FROM table_name;
  • DELETE * FROM table_name;
• How to delete a table?
  • DROP TABLE table_name; // Only when the table is empty?
• How to delete a database?
  • DROP DATABASE database_name;

• Let's use a table. 'Users'.
  • Columns: Id (primary key, auto increment), Username, Password, Email, Date
  • If you did not create the table, it is about the time to create it using phpMyAdmin.
  • Trial 9: Let's try to check if a user exists in Users. You need to use a function.
    <?php $conn = mysqli_connect('???', '???', '???', '???'); if (mysqli_connect_errno()) echo 'Failed to connect: ' . mysqli_connect_error(); ???? { // a function to check whether a username exists ???? $sql = "????"; // select rows from Users where the Username column is equal to $username ???? } //------------------------------- $username = "John"; if (????) echo "$username exists.<br>"; else echo "$username does not exist.<br>"; ?> Submit the code to the server to execute PHP code
    
  • Trial 10: Let's try to insert a new user and get its user id. You need to use a function. (E.g., "???? values (NULL, ???, ???, ???, ???)")
    <?php $conn = mysqli_connect('???', '???', '???', '???'); if (mysqli_connect_errno()) echo 'Failed to connect: ' . mysqli_connect_error(); ???? { // a function to insert username and password into Users and // to returns the id of the newly inserted row ???? $sql = "????"; ???? } //------------------------------ $username = "John"; $password = "secret"; $email = "john@tru.ca"; $current_date = ????; // date('Ymd'); $id = ????; echo "Id = " . $id . "<br>"; ?> Submit the code to the server to execute PHP code
    

• Let's use a table. 'Questions'.
  • Columns: Id (primary key), Question, Private, UserId, Date

• Can you write a function to check the validity of a given username and password?
• How can you retrieve all the questions that include a search term?

  $sql = "... where Question like '%search-term%'";
  

• How to retrieve 10 questions that were posted most recently?
  limit #; limit #,#
• How can you retrieve all the questions that were posted by a certain user and that include a search term?
• How can you retrieve all the answers for a particular question?
• How to retrive all the questions posted by a certain user, with all the answers?

$sql = "select * from Users where Username = '$username'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$userId = $row['Id'];
$sql = "select * from Questions where UserId = $userId";
$result = mysqli_query($conn, $sql);
$table = "<table><tr><th>Question</th><th>Date</th></tr>";
while($row = mysqli_fetch_assoc($result))
    $table .= "<tr><td>" . $row['Question'] . "</td><td>" . $row['Date'] . "</td></tr>";
$table .= "</table>";
echo $table;